using System;
using System.Data;
using System.Data.Common;
using System.Configuration;


namespace ForGVN
{
    /// <summary>
    /// Class contains generic data access functionality to be accessd from the business tier
    /// </summary>
    public static class GenericDataAccess
    {
        static GenericDataAccess()
        {

        }

        public static DataTable ExecuteSelectCommand(DbCommand command)
        {
            //The Datatable to be returned
            DataTable table = null;
            //Execute the command making sure the connection gets closed in the end
            try
            {
                //Open the data connection
                command.Connection.Open();
                //Execute the command and save the results in a DataTable
                DbDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);
                //Close the reader
                reader.Close();
            }
            catch(Exception err)
            {

            }
            finally
            {
                //Close the connection
                command.Connection.Close();
            }
            return table;
        }

        //creates and prepares a new DbCommand object on a new connection

        public static DbCommand CreateCommand()
        {
            //Obtain the database provider name
            string dataProviderName = "System.Data.SqlClient";
            //Obtain the database connection string
           // string connectionString = "Server=(local)\\SQLEXPRESS;Integrated Security=True;Database=NT090108SQL";
            string connectionString = "Data Source = 192.168.10.2\\SQLEXPRESS; Initial Catalog = NT090108SQL; User = sa; Password = abc123+-*/";
            //Create a new data provider factory
            DbProviderFactory factory = DbProviderFactories.GetFactory(dataProviderName);
            //Obtain a database specific connection object
            DbConnection conn = factory.CreateConnection();
            //Set the connection string
            conn.ConnectionString = connectionString;
            //conn.
            //Create a database specific command object
            DbCommand comm = conn.CreateCommand();
            //Set the command type to stored procedure
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandTimeout = 600;
            //Return the initialized command object
            return comm;
        }

        // execute an update, delete, or insert command 
        // and return the number of affected rows
        public static int ExecuteNonQuery(DbCommand command)
        {
            // The number of affected rows 
            int affectedRows = -1;
            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();
                // Execute the command and get the number of affected rows
                affectedRows = command.ExecuteNonQuery();
            }
            catch(Exception err)
            {
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }
            // return the number of affected rows
            return affectedRows;
        }

        // execute a select command and return a single result as a string
        public static string ExecuteScalar(DbCommand command)
        {
            // The value to be returned 
            string value = "";
            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();
                // Execute the command and get the number of affected rows
                value = command.ExecuteScalar().ToString();
            }
            catch
            {
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }
            // return the result
            return value;
        }
    }

}